package rak.r91.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import rak.r91.data.Item;
import rak.r91.data.ItemAttribute;

public class JdbcItemAttributeDao extends JdbcDaoSupport implements ItemAttributeDao {
	
	@Override
	public List<ItemAttribute> getItemAttributes(int itemTypeID) {		
		return getJdbcTemplate().query("SELECT IAT.type_name,TA.item_attribute_type_fk,TA.required,TA.orderby,IAT.data_type FROM type_attribute TA INNER JOIN item_attribute_type IAT "+ 
				"ON TA.item_attribute_type_fk = IAT.item_attribute_type WHERE TA.item_type_fk="+itemTypeID+" AND TA.created_by_default='Y'", new itemMapper());
	}
	
	private static final class itemMapper implements RowMapper<ItemAttribute> {
	    public ItemAttribute mapRow(ResultSet rs, int rowNum) throws SQLException {
	        ItemAttribute itemAttribute = new ItemAttribute();
	        itemAttribute.setName(rs.getString("type_name"));
	        itemAttribute.setItemAttributeTypeID(rs.getInt("item_attribute_type_fk"));
	        itemAttribute.setRequired(rs.getString("required"));
	        itemAttribute.setOrderBy(rs.getInt("orderby"));
	        itemAttribute.setDataType(rs.getInt("data_type"));
	        return itemAttribute;
	    }
	}

	@Override
	public int insertAttributeText(ItemAttribute itemAttribute, Item item) {
		getJdbcTemplate().update("INSERT INTO item_attribute (item_fk,item_attribute_type_fk,value_text,data_type,orderby)"+
		"VALUES ("+item.getItemID()+", "+itemAttribute.getItemAttributeTypeID()+",'"+itemAttribute.getValueText()+"',"+itemAttribute.getDataType()+","+itemAttribute.getOrderBy()+")");
		return 0;
	}

	@Override
	public int insertAttributeNr(ItemAttribute itemAttribute, Item item) {
		getJdbcTemplate().update("INSERT INTO item_attribute (item_fk,item_attribute_type_fk,value_number,data_type,orderby)"+
				"VALUES ("+item.getItemID()+", "+itemAttribute.getItemAttributeTypeID()+",'"+itemAttribute.getValueText()+"',"+itemAttribute.getDataType()+","+itemAttribute.getOrderBy()+")");
		return 0;
	}
	
	public List<ItemAttribute> getItemAtrributesAndValues(Item item){
		return getJdbcTemplate().query("SELECT IA.item_attribute, IAT.type_name,IAT.data_type, IA.value_text,IA.value_number, IA.orderby, TA.required FROM item I LEFT JOIN item_attribute IA ON I.item = IA.item_fk INNER JOIN item_attribute_type IAT ON IA.item_attribute_type_fk =IAT.item_attribute_type INNER JOIN type_attribute TA ON TA.item_attribute_type_fk=IAT.item_attribute_type WHERE I.item="+item.getItemID(), 
				new attributeMapper());
	}
	
	private static final class attributeMapper implements RowMapper<ItemAttribute> {
	    public ItemAttribute mapRow(ResultSet rs, int rowNum) throws SQLException {
	        ItemAttribute itemAttribute = new ItemAttribute();
	        itemAttribute.setItemAttributeID(rs.getInt("item_attribute"));
	        itemAttribute.setName(rs.getString("type_name"));
	        itemAttribute.setDataType(rs.getInt("data_type"));
	        itemAttribute.setValueText(rs.getString("value_text"));
	        itemAttribute.setValueNumber(rs.getDouble("value_number"));
	        itemAttribute.setOrderBy(rs.getInt("orderby"));
	        itemAttribute.setRequired(rs.getString("required"));
	        return itemAttribute;
	    }
	}

	@Override
	public int updateItemAttribute(ItemAttribute itemAttribute) {
		return getJdbcTemplate().update("UPDATE item_attribute SET value_text = ?, value_number = ? WHERE item_attribute = ?", new Object[]{itemAttribute.getValueText(),itemAttribute.getValueNumber(),itemAttribute.getItemAttributeID()});
	}
	
	


}
